const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *销退接口
 */

//  销退开始时间
let startTime = '2019-05-17 00:00:00';
// let startTime = '';
//  获得订单信息

const getVerifyReturnGoodsOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,J.XH,Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,Z.FKQX,Z.FKQZ,Z.PJLX,Z.PJHM,Z.PZH,Z.ZJS,Z.ZSS,Z.ZHJ,Z.ID_XSD,J.BM,J.MC,J.KM1BM,J.KMBM,J.KM2BM,J.KM3BM,J.KMQC,J.LX,B.DH FROM XTD_ZB Z LEFT JOIN XTD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND Z.RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}' AND Z.SH = 'F' `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM XTD_ZB Z LEFT JOIN XTD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND Z.RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}' AND Z.SH= 'F' `
    if (body.searchVal.DWMC) {
        startSql += ` AND (B.MC LIKE '%${body.searchVal.DWMC}%' OR B.DH LIKE '%${body.searchVal.DWMC}%')`;
        totalSql += ` AND (B.MC LIKE '%${body.searchVal.DWMC}%' OR B.DH LIKE '%${body.searchVal.DWMC}%')`;
    }
    if (body.searchVal.ID) {
        startSql += ` AND Z.ID = '${body.searchVal.ID}' `;
        totalSql += ` AND Z.ID = '${body.searchVal.ID}' `;
    }
    if (body.searchVal.ID_DHD) {
        startSql += ` AND Z.ID_XSD = '${body.searchVal.ID_DHD}' `;
        totalSql += ` AND Z.ID_XSD = '${body.searchVal.ID_DHD}' `;
    }
    if (body.searchVal.YPMC) {
        startSql += ` AND Z.ID IN (SELECT M.ID FROM XTD_MX M LEFT JOIN BM_YP Y ON M.YPBM = Y.BM WHERE M.YPBM  LIKE '%${body.searchVal.YPMC}%' OR M.YPTM  LIKE '%${body.searchVal.YPMC}%' OR M.YPMC  LIKE '%${body.searchVal.YPMC}%' OR Y.JP  LIKE '%${body.searchVal.YPMC}%') `;
        totalSql += ` AND Z.ID IN (SELECT M.ID FROM XTD_MX M LEFT JOIN BM_YP Y ON M.YPBM = Y.BM WHERE M.YPBM  LIKE '%${body.searchVal.YPMC}%' OR M.YPTM  LIKE '%${body.searchVal.YPMC}%' OR M.YPMC  LIKE '%${body.searchVal.YPMC}%' OR Y.JP  LIKE '%${body.searchVal.YPMC}%') `;
    }
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    startSql += endSql;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}


//  获得订单对应的商品信息
const getVerifyReturnShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT M.YPBM,M.YPMC,M.SCPH,M.YPGG,M.JLDW,M.JXMC,M.CKBM,M.CKMC,M.SCRQ,M.YXQZ,M.SCCJ,M.PZWH,M.HWBH,Y.YPLX,M.XH,M.CBJJ,Y.CCTJ,
    M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,Y.JP,M.SL,M.JE,Y.MJSL,M.JS ,M.SS,M.HJ,Z.BMBM,Y.SSXK FROM XTD_MX M LEFT JOIN XTD_ZB Z 
    ON M.ID = Z.ID LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM   WHERE M.ID = '${body.orderId}' `;
    let result = await sql.query(startSql);
    // 获取商品在库存动态表存在过的历史批号
    let shopList = result.recordset;
    for (let shop of shopList) {
        let historyPhSql = `SELECT SCPH FROM KC_KCDTB WHERE BMBM = '${shop.BMBM}' AND CKBM ='${shop.CKBM}' AND YPBM = '${shop.YPBM}' GROUP BY SCPH`;
        let historyPh = await sql.query(historyPhSql);
        shop.scphList = historyPh.recordset;
    }
    ctx.body = {
        data: result.recordset
    };
}


// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}

//  保存订单信息
const keepVerifyReturnInfor = async (ctx, next) => {
    const body = ctx.request.body;
    let shopInfor = body.entryList;
    let chooseOrderList = body.chooseOrderList;
    let actionSqlList = [];
    let zje = 0, zss = 0, zjs = 0, zsl = 0, zcb = 0, zml = 0;
    // 更新销退明细表
    for (let item of shopInfor) {
        // shopInfor.forEach(async (item, ind) => {
        let dj = Number(item.DJ ? item.DJ : 0);
        let thsl = Number(item.THSL ? item.THSL : 0);
        let mjsl = Number(item.MJSL ? item.MJSL : 0);
        let cbjj = Number(item.CBJJ ? item.CBJJ : 0);
        let je = dj * thsl;
        let js = mjsl === 0 ? 0 : Math.floor(thsl / mjsl);
        let ss = thsl - (mjsl * js);
        let xscb = Number((cbjj * thsl).toFixed(2));
        let xsml = Number(((dj - cbjj) * thsl).toFixed(2));
        zcb += xscb;
        zml += xsml;
        zje += je;
        zss += ss;
        zjs += js;
        zsl += thsl;
        let updateMxSql = `UPDATE XTD_MX SET XS= ${item.THSL},SL = ${item.THSL},JE = ${je},JS= ${js},SS= ${ss},XSCB = ${xscb},XSML = ${xsml},SCPH='${item.SCPH}',SCRQ='${item.SCRQ}',YXQZ='${item.YXQZ}' WHERE ID = '${chooseOrderList.ID}' AND XH = '${item.XH}' AND YPBM = '${item.YPBM}'`;
        // await sql.query(updateMxSql);
        actionSqlList.push(updateMxSql);
        // 更新库存动态表
        let oldStock = await sql.query(`SELECT KCSL,KYSL,CBJJ,SCRQ,YXQX,YXQZ FROM KC_KCDTB WHERE  BMBM='${chooseOrderList.BMBM}' AND CKBM='${item.CKBM}' AND YPBM = '${item.YPBM}' AND SCPH='${item.SCPH}' `);
        let oldStockResult = oldStock.recordset[0];
        if (oldStock.rowsAffected[0] > 0) {
            let newKcsl = Number(oldStockResult.KCSL) + thsl;
            let newKcje = (newKcsl * oldStockResult.CBJJ).toFixed(2);
            let kysl = Number(oldStockResult.KYSL) + thsl;
            let updateKcSql = `UPDATE KC_KCDTB SET KCSL = ${newKcsl},KCJE = ${newKcje},HWBH='${item.HWBH}',KYSL = ${kysl} `;
            if (!oldStock.SCRQ) {
                updateKcSql += `,SCRQ = '${item.SCRQ}'`;
            }
            if (!oldStock.YXQX) {
                updateKcSql += `,YXQX = '${item.YXQX}'`;
            }
            if (!oldStock.YXQZ) {
                updateKcSql += `,YXQZ = '${item.YXQZ}'`;
            }

            updateKcSql += ` WHERE BMBM='${chooseOrderList.BMBM}' AND CKBM='${item.CKBM}' AND YPBM = '${item.YPBM}' AND SCPH='${item.SCPH}'`;
            // await sql.query(updateKcSql);
            actionSqlList.push(updateKcSql);
        } else {
            let insertKcSql = `INSERT INTO KC_KCDTB(BMBM,CKBM,CKMC,YPBM,SCPH,RKRQ,SCRQ,YXQX,YXQZ,KCSL,KYSL,CBJJ,KCJE,DWBM,DWMC,HWBH,YHRQ) VALUES 
            ('${item.BMBM}','${item.CKBM}','${item.CKMC}','${item.YPBM}','${item.SCPH}','${baseFn.getNowTimeId().fullDate}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
            ${item.THSL},${item.THSL},${item.CBJJ},${(item.CBJJ * item.THSL).toFixed(2)},'','','${item.HWBH}','')`;
            // await sql.query(insertKcSql);
            actionSqlList.push(insertKcSql);
        }
        // });
    }
    // 更新总表和结算表
    let updateJsSql = `UPDATE XTD_JS SET JSJE = ${zje} WHERE ID = '${chooseOrderList.ID}' `;
    let updateZbSql = ` UPDATE XTD_ZB SET ZJE = ${zje},ZCB = ${zcb},ZML = ${zml},ZSS = ${zss},ZJS = ${zjs},ZSL = ${zsl},SH='T',SHR = '${body.SHR}', 
    XJS = ${zje},EJS = ${zje},WJS = 0 WHERE ID = '${chooseOrderList.ID}'`;
    actionSqlList.push(updateZbSql);
    actionSqlList.push(updateJsSql);
    // let zbResult = await sql.query(updateZbSql);
    // let jsResult = await sql.query(updateJsSql);
    let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
    console.log(lastSql);
    let result = await sql.query(lastSql);
    if (result.rowsAffected[0]) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }


}

module.exports = {
    'POST /getVerifyReturnGoodsOrder': getVerifyReturnGoodsOrder,
    'POST /getVerifyReturnShop': getVerifyReturnShop,
    'POST /keepVerifyReturnInfor': keepVerifyReturnInfor
}
